package com.android.music.prolyric.database;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import com.android.music.prolyric.SimpleAdapterSearch;
import com.android.music.prolyric.search.ConnectionDetector;

import android.content.Context;
import android.database.Cursor;
import android.os.AsyncTask;
import android.util.Log;
import android.widget.SimpleAdapter;

public class DatabaseManger {
	//private Cursor mCusor;
	
	private DBAdapter db = null;
	private static final String URL_GET_BY_NAME = "http://vienlemaibk08.byethost3.com/get_lyric_by_name.php";
	private static final String URL_INSERT_LYRIC ="http://vienlemaibk08.byethost3.com/create_lyric.php";
	JSONParser jsonParser = new JSONParser();
	private  static final  String loginUrl = "http://loidich.com/index.php?do=login&act=sm";
	private   static final String getAccountUrl = "http://vienlemaibk08.byethost3.com/get_account.php";
	private  static InputStream is = null;
	private static String json = "";
	public static SimpleAdapterSearch simpleAdapter=null;
	public static ArrayList<HashMap<String, String>> arrayList=null;
	public static String username = "";
	public static String password = "";
	private ConnectionDetector cd;
	
	public DatabaseManger(Context context) {
		
		db = new DBAdapter(context);
		if(username.equals(""))
		{
			cd = new ConnectionDetector(context);
			if(cd.isConnectingToInternet())
				{
					DatabaseManger.getAccount();
					Log.i("Network", "on");
				}
			else
				Log.i("Network", "off");
		}
	}
	
	public boolean updateLyric(LyricDTO l)
	{
		l.getStandarLyric();
		boolean check = false;
		db.open();
		
		try 
		{
			check = db.updateLyrics(l.getSongName(), l.getSinger(), l.getLyricEn(), l.getLyricVi());
			Log.i("update",check+" thanh cong");
		} 
		
		catch (Exception e) 
		{
			Log.d("Error Database", "Error update Lyric"+e);
		}
		finally
		{
			
			db.close();
		}
		
		Log.i("update1",check+"");		
		
		return check;
	}
	
	public boolean insertLyric(LyricDTO l)
	{
		boolean check = false;
		db.open();
		
		try 
		{
			db.insertLyrics(l.getSongName().trim(), l.getSinger().trim(), l.getLyricEn(), l.getLyricVi());
			check = true;
			Log.d("insert Database", "thanh cong");
		} 
		catch (Exception e) 
		{
			Log.d("Error Database", "Error insert Lyric"+e);
		}
		finally
		{
			db.close();
			
		}
		
		return check;
	}
	
	public LyricDTO getLyricById(int id)
	{
		LyricDTO l = null;
		db.open();
		
		Cursor mCusor = null;
		try 
		{
			mCusor = db.getLyricById(id);
			if(mCusor.moveToFirst())
			{
				l = new LyricDTO();
				l.setId(mCusor.getInt(mCusor.getColumnIndex("id")));
				l.setSongName(mCusor.getString(mCusor.getColumnIndex("songname")));
				l.setSinger(mCusor.getString(mCusor.getColumnIndex("singer")));
				l.setLyricEn(mCusor.getString(mCusor.getColumnIndex("lyricEn")));
				l.setLyricVi(mCusor.getString(mCusor.getColumnIndex("lyricVi")));				
				
			}
		} 
		
		catch (Exception e) 		
		{
			Log.d("Error Database", "Error get Lyric by id"+e);
		}
		
		finally
		{
			db.close();
			if(mCusor != null && !mCusor.isClosed())
				mCusor.close();			
		} 
		
		return l;
	}
	
	public ArrayList<HashMap<String, String>> getLyricByName(String songname, String singer)
	{
		ArrayList<HashMap<String, String>> result = null;
		songname = songname.replaceAll("'", "''").trim();
		singer = singer.replaceAll("'", "''").trim();
		
		db.open();
		Cursor mCusor = null;
		try 
		{
			mCusor = db.getLyricByName(songname, singer);
			if(mCusor==null)
				Log.i("Lay dl", "ko co dl");
			if(mCusor.moveToFirst()) 
			{
				Log.i("Lay dl", "thanh cong");
				result = new ArrayList<HashMap<String,String>>();
				
				HashMap<String, String> hm = new HashMap<String, String>();
				hm.put("songname", mCusor.getString(mCusor.getColumnIndex("songname")));
				hm.put("singer", mCusor.getString(mCusor.getColumnIndex("singer")));
				hm.put("lyricEn", mCusor.getString(mCusor.getColumnIndex("lyricEn")));
				hm.put("lyricVi", mCusor.getString(mCusor.getColumnIndex("lyricVi")));
				result.add(hm);
				Log.i("Lay dl", "thanh cong");
			}
			
		} 
		
		catch (Exception e) 
		{
			Log.d("Error Database", "Error get Lyric by name + \n"+e);
		}
		
		finally
		{
			db.close();
			if(mCusor != null && !mCusor.isClosed())
				mCusor.close();
		}
		return result;
	}
	
		
	/*public boolean updateLyric(LyricDTO l)
	{
		l.getStandarLyric();
		boolean check = false;
		
		String sql = "update into lyric set lyricEn = '"+l.getLyricEn()+"', lyricVi = '"+l.getLyricVi()+"'" +
				"where songname = '"+l.getSongName()+"'";
		//Log.d("insert_database_sql", sql);
		try {
			Log.i("update0",check+"");
			service.exeSQL(sql);
			check = true;
			Log.i("update",check+"");
		} catch (Exception e) {
			Log.d("Error Database", "Error insert Lyric"+e);
		}
		finally
		{
			service.close();
		}
		Log.i("update",check+"");
		
		
		return check;
	}*/
	
	/*
	 * insert a lyric to database
	 * */
	/*
	public boolean insertLyric(LyricDTO l)
	{
		
		l.getStandarLyric();
		boolean check = false;
		String sql = "insert into lyric (songname, singer, lyricEn, lyricVi) values "+
				"('"+l.getSongName()+"','"+l.getSinger()+"','"+l.getLyricEn()+"','"+l.getLyricVi()+"')";
		//Log.d("insert_database_sql", sql);
		try {
			service.exeSQL(sql);
			check = true;
		} catch (Exception e) {
			Log.d("Error Database", "Error insert Lyric"+e);
		}
		finally
		{
			service.close();
		}
		
		return check;
	}
	
	public LyricDTO getLyricById(int id)
	{
		LyricDTO l = null;
		String sql = "select * from lyric where id = " + id;
		Cursor mCusor = null;
		try {
			 mCusor = service.queryData(sql);
			if(mCusor.moveToFirst())
			{
				l = new LyricDTO();
				l.setId(mCusor.getInt(mCusor.getColumnIndex("id")));
				l.setSongName(mCusor.getString(mCusor.getColumnIndex("songname")));
				l.setSinger(mCusor.getString(mCusor.getColumnIndex("singer")));
				l.setLyricEn(mCusor.getString(mCusor.getColumnIndex("lyricEn")));
				l.setLyricVi(mCusor.getString(mCusor.getColumnIndex("lyricVi")));
				
				
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		
		finally{
			service.close();
			if(mCusor != null && !mCusor.isClosed())
				mCusor.close();
			//mCusor.close();
		} 
		return l;
	}
	
	public ArrayList<HashMap<String, String>> getLyricByName(String songname, String singer)
	{
		ArrayList<HashMap<String, String>> result = null;
		songname = songname.replaceAll("'", "''").trim();
		LyricDTO l = null;
		String sql = "select * from lyric where songname = '"+songname.toLowerCase()+"'and singer = '"+singer.toLowerCase().trim()+"'" ;
		Log.d("sql select", sql);
		Cursor mCusor = null;
		try {
			 mCusor = service.queryData(sql);
			if(mCusor.moveToFirst()) 
			{
				result = new ArrayList<HashMap<String,String>>();
				
				l = new LyricDTO();
				l.setId(mCusor.getInt(mCusor.getColumnIndex("lyricId")));
				l.setSongName(mCusor.getString(mCusor.getColumnIndex("songname")));
				l.setSinger(mCusor.getString(mCusor.getColumnIndex("singer")));
				l.setLyricEn(mCusor.getString(mCusor.getColumnIndex("lyricEn")));
				l.setLyricVi(mCusor.getString(mCusor.getColumnIndex("lyricVi")));
				
					HashMap<String, String> hm = new HashMap<String, String>();
					hm.put("songname", mCusor.getString(mCusor.getColumnIndex("songname")));
					hm.put("singer", mCusor.getString(mCusor.getColumnIndex("singer")));
					hm.put("lyricEn", mCusor.getString(mCusor.getColumnIndex("lyricEn")));
					hm.put("lyricVi", mCusor.getString(mCusor.getColumnIndex("lyricVi")));
					result.add(hm);
			
				
				
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally
		{
			service.close();
			if(mCusor != null && !mCusor.isClosed())
				mCusor.close();
		}
		return result;
	}*/
	
	
	public ArrayList<HashMap<String, String>> getLyricFromHost(String songname, String singer)
	{
		
		ArrayList<HashMap<String, String>> result = null;
		
		Log.d("get From Host", songname + "  " + singer);
		
		List<NameValuePair> listparam = new ArrayList<NameValuePair>();
		listparam.add(new BasicNameValuePair("songname",songname.trim()));
		listparam.add(new BasicNameValuePair("singer",singer.trim()));
		JSONParser jsonParser = new JSONParser();
		int success;
		// TODO Auto-generated method stub
         // getting product details by making HTTP request
         // Note that product details url will use GET request
		try
		{
         JSONObject json = jsonParser.makeHttpRequest(URL_GET_BY_NAME, "GET", listparam);
         if(json==null)
        	 Log.d("get lyric from host", "json is null");
        	
         else{
        	 Log.d("get lyric from host", json.toString());
         
         // json success tag
         success = json.getInt("success");
         if (success == 1) {
        	 result = new ArrayList<HashMap<String,String>>();
             // successfully received product details 
             JSONArray lyricObject = json .getJSONArray("lyric"); // JSON Array
             HashMap<String, String> hm = new HashMap<String, String>();
             // get first product object from JSON Array
             JSONObject lyric = lyricObject.getJSONObject(0);
             hm.put("songname", lyric.getString("songname"));
             hm.put("singer", lyric.getString("singer"));
             hm.put("lyricEn", lyric.getString("lyricEn"));
             hm.put("lyricVi", lyric.getString("lyricVi"));
            result.add(hm);
            
           
             // product with this pid found
             // Edit Text
            

         }else{
             
         }
		}
     } catch (JSONException e) {
         e.printStackTrace();
     }



			return result;

		
	}
	
	
	
	
	
	public void insertLyricToHost(LyricDTO lyric)
	{
		
		 List<NameValuePair> params = new ArrayList<NameValuePair>();
         params.add(new BasicNameValuePair("songname", lyric.getSongName().trim()));
         params.add(new BasicNameValuePair("singer", lyric.getSinger().trim()));
         params.add(new BasicNameValuePair("lyricEn", lyric.getLyricEn()));
         params.add(new BasicNameValuePair("lyricVn", lyric.getLyricVi()));

         // getting JSON Object
         // Note that create product url accepts POST method
         JSONObject json = jsonParser.makeHttpRequest(URL_INSERT_LYRIC,"POST", params);
         
         try {
        	 int success = json.getInt("success");
        	 if(success == 1)
        	 {
        		 Log.d("Insert_Host", "success insert lyric to host ");
        		 
        	 }
        	 else {
        		 Log.d("Insert_Host", "Error to insert host");
        		 Log.d("Insert_Host", json.getString("message"));
        	 }
			
		} catch (Exception e) {
			// TODO: handle exception
		}
         // check log cat fro response
         Log.d("Create Response", json.toString());

       
	}
	
	public static void getAcountOnHost()
	{
		new GetAccountFormHost().execute("");
	}
	
	
	 public static String[] getAccount()
		{
		 String [] result = new String[2];
			JSONObject jObj = null;
			 try {
		    	   DefaultHttpClient httpClient = new DefaultHttpClient();
		           HttpPost httpPost = new HttpPost(getAccountUrl);
		           

		           HttpResponse httpResponse = httpClient.execute(httpPost);
		           HttpEntity httpEntity = httpResponse.getEntity();
		           is = httpEntity.getContent();
			} catch (Exception e) {
				Log.d("catch", e.toString());
			}
		       if(is == null)
		       {
		       	Log.d("ERROR", "khong ket noi duoc");
		       }
		       else 
		       {
		       	Log.d("success", ""+is.toString());
		       }

		       try {
		           BufferedReader reader = new BufferedReader(new InputStreamReader(
		                   is, "UTF-8"), 8);
		           StringBuilder sb = new StringBuilder();
		           String line = null;
		           while ((line = reader.readLine()) != null) {
		               sb.append(line + "\n");
		           }
		           is.close();
		           json = sb.toString();
		           Log.d("respone", json);
		       } catch (Exception e) {
		           Log.e("Buffer Error", "Error converting result " + e.toString());
		       }
		       
		       
		       try {
		            jObj = new JSONObject(json);
		            JSONArray lyricObject = jObj .getJSONArray("lyric"); // JSON Array

		             // get first product object from JSON Array
		             JSONObject lyric = lyricObject.getJSONObject(0);
		            result[0] = lyric.getString("username");
		            result[1] = lyric.getString("password");
		            
		 	      
		        } catch (JSONException e) {
		            Log.e("JSON Parser", "Error parsing data " + e.toString());
		        }
		       
		       
		       
		      return result;
		       
		}
	
	private static class GetAccountFormHost extends AsyncTask<String, Integer, String[]>
	{

		@Override
		protected void onPostExecute(String[] result) {
			// TODO Auto-generated method stub
			if(result != null)
			{
				username = result[0];
				password = result[1];
				Log.d("databaseManager", username+"  "+password);
			}
		}

		@Override
		protected String[] doInBackground(String... params) {
			return getAccount();
			
		}
		
	}
	

}
